package cmpe226.spring2011.project1;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * TODO: Implement :D
 * 
 * 
 */
public class BigDAO {

	private String url, user, pw;

	public BigDAO(String dburl, String dbuser, String dbpw)
			throws ClassNotFoundException {
		// Load PostgreSQL JDBC driver
		Class.forName("org.postgresql.Driver");

		url = "jdbc:postgresql://" + dburl;
		user = dbuser;
		pw = dbpw;
	}

	public void save(List<Symbol> symlist) throws SQLException {
		Connection conn = null;

		try {
			conn = DriverManager.getConnection(url, user, pw);
			for (Symbol sym : symlist) {
				saveSymbol(conn, sym);
			}
		} finally {
			DAOHelper.close(conn);
		}
	}

	public void save(Symbol symbol) throws SQLException {
		Connection conn = null;

		try {
			conn = DriverManager.getConnection(url, user, pw);
			saveSymbol(conn, symbol);
		} finally {
			DAOHelper.close(conn);
		}
	}

	public Symbol read(String symbol) throws SQLException {
		Connection conn = null;
		PreparedStatement selectQuotes = null;

		String selectQuotesString = "select date,open,high,low,close,volume,adjclose from symbols,quotes where symbols.symbol = ? and symbols.symbol_id = quotes.symbol";

		try {
			conn = DriverManager.getConnection(url, user, pw);

			selectQuotes = conn.prepareStatement(selectQuotesString);
			selectQuotes.setString(1, symbol);
			ResultSet results = selectQuotes.executeQuery();

			// Get all quotes
			List<Quote> quotes = new ArrayList<Quote>();
			while (results.next()) {
				Quote quote = new Quote();
				quote.setDate(results.getDate("date"));
				quote.setOpen(results.getDouble("open"));
				quote.setHigh(results.getDouble("high"));
				quote.setLow(results.getDouble("low"));
				quote.setClose(results.getDouble("close"));
				quote.setVolume(results.getInt("volume"));
				quote.setAdjclose(results.getDouble("adjclose"));
				quotes.add(quote);
			}

			// Make the symbol object
			return new Symbol(symbol, quotes);
		} finally {
			DAOHelper.close(selectQuotes);
			DAOHelper.close(conn);
		}
	}

	public List<DifferenceDTO> getSymbolDifferences(String from, String to, List<String> symbols) throws SQLException, ParseException {
		Connection conn = null;
		PreparedStatement selectDifferences = null;
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		
		String symbolChars = "";
		Iterator<String> it = symbols.iterator();
		while(it.hasNext()) {
			 symbolChars += "'"+it.next()+"'";
			 if(it.hasNext()) {
				 symbolChars +=",";
			 }
		}
		
		String selectString = "select todays_quotes.symbol_char, todays_quotes.date, " +
				"(todays_quotes.close - todays_quotes.open)/todays_quotes.open as difference from " +
				"(select quotes.date, open, close, symbols.symbol as symbol_char from quotes " +
				"inner join symbols on symbols.symbol_id = quotes.symbol where quotes.date between date '"+from+"' and date '"+to+"') " +
				"as todays_quotes where todays_quotes.symbol_char in ("+symbolChars+") order by todays_quotes.symbol_char, todays_quotes.date asc";
		
		conn = DriverManager.getConnection(url, user, pw);
		selectDifferences = conn.prepareStatement(selectString);
		
		List<DifferenceDTO> diffs = new ArrayList<DifferenceDTO>();
		try {
			ResultSet results = selectDifferences.executeQuery();
			
			while (results.next()) {
				//System.out.println(results.getString("symbol_char"));
				DifferenceDTO diff = new DifferenceDTO();
				diff.setDate(df.format(results.getDate("date")));
				diff.setSymbol(results.getString("symbol_char"));
				diff.setDifference(results.getDouble("difference"));
				diffs.add(diff);
			}
			return diffs;
		} finally {
			DAOHelper.close(selectDifferences);
			DAOHelper.close(conn);
		}
	}
	
	public List<DifferenceDTO> companyCompare(String from, String to, List<String> symbols) throws SQLException, ParseException {
		Connection conn = null;
		PreparedStatement selectDifferences = null;
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String[] selectString = new String[symbols.size()];
		
		conn = DriverManager.getConnection(url, user, pw);
		List<DifferenceDTO> company = new ArrayList<DifferenceDTO>();
		
		for(int i = 0; i < symbols.size(); i++) {
			selectString[i] ="select date,close,symbols.symbol from quotes inner join symbols on symbols.symbol_id=quotes.symbol "+ 
				"where symbols.symbol='"+symbols.get(i)+"' and quotes.date in ('"+from+"', '"+to+"');";
			
			selectDifferences = conn.prepareStatement(selectString[i]);
			
			try {
				ResultSet results = selectDifferences.executeQuery();				
				while (results.next()) {
					DifferenceDTO diff = new DifferenceDTO();
					diff.setDate(df.format(results.getDate("date")));
					diff.setSymbol(results.getString("symbol_char"));
					diff.setDifference(results.getDouble("difference"));
					company.add(diff);
				}
			} finally {
				DAOHelper.close(selectDifferences);
				DAOHelper.close(conn);
			}
		}
		return company;
	}

	
    private void saveSymbol(Connection conn, Symbol symbol) {
        PreparedStatement insertSymbol = null;
        PreparedStatement selectSymbol = null;
        PreparedStatement insertQuote  = null;
        
        String insertSymbolString = "insert into symbols(symbol) values (?)";
        String insertQuoteString = "insert into quotes(symbol, date, open, high, low, close, volume, adjclose) values ((select currval('symbols_symbol_id_seq')),?,?,?,?,?,?,?)";
        
        List<Quote> quotes = symbol.getQuotes();
        
        try {
    	    conn.setAutoCommit(false);
            
            // Insert symbol
            insertSymbol = conn.prepareStatement(insertSymbolString);
            insertSymbol.setString(1, symbol.getSymbol());
            insertSymbol.executeUpdate();
            
            // Insert all of the quotes
            insertQuote = conn.prepareStatement(insertQuoteString);
            for (Quote quote : quotes) {
                insertQuote.setDate  (1, new java.sql.Date(quote.getDate().getTime()));
                insertQuote.setDouble(2, quote.getOpen());
                insertQuote.setDouble(3, quote.getHigh());
                insertQuote.setDouble(4, quote.getLow());
                insertQuote.setDouble(5, quote.getClose());
                insertQuote.setInt   (6, quote.getVolume());
                insertQuote.setDouble(7, quote.getAdjclose());
                insertQuote.executeUpdate();
            }
    
            conn.commit();
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException sqle) {
                    sqle.printStackTrace();
                }
            }
        } finally {
            DAOHelper.close(insertSymbol);
            DAOHelper.close(selectSymbol);
            DAOHelper.close(insertQuote);
        }
    }
}
